using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_NOTICIA : DALConnection
    {

        #region Incluir

        public void incluir(T_NOTICIA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_NOTICIA ");
                SQL.Append("    (DT_NOTICIA, DT_VIGENCIA, DT_INS_NOTICIA, TX_TITULO, TX_DESC_NOTICIA, TX_URL_FOTO_PEQ, TX_URL_FOTO_GRDE, TX_FONTE, TX_REDATOR) ");
                SQL.Append(" VALUES ");
                SQL.Append("    (str_to_date('" + obj.DT_NOTICIA.ToString("dd/MM/yyy") + "', '%d/%m/%Y'),");
                
                if (!obj.DT_VIGENCIA.ToString("dd/MM/yyyy").Equals(""))
                    SQL.Append("str_to_date('" + obj.DT_VIGENCIA.ToString("dd/MM/yyy") + "', '%d/%m/%Y'), ");
                else
                    SQL.Append("null, ");
                SQL.Append("str_to_date('" + obj.DT_INS_NOTICIA.ToString("dd/MM/yyy") + "', '%d/%m/%Y'), '" + obj.TX_TITULO + "', '");
                SQL.Append(obj.TX_DESC_NOTICIA + "', '" + obj.TX_URL_FOTO_PEQ.Replace("\\", "#") + "','" + obj.TX_URL_FOTO_GRDE.Replace("\\", "#") + "', '" + obj.TX_FONTE + "','");
                SQL.Append(obj.TX_REDATOR + "') ");

                DbCommand insertT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());

                insertT_NOTICIA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_NOTICIA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_NOTICIA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_NOTICIA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_NOTICIA o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_NOTICIA ");
                    SQL.Append("    (ID_NOTICIA, DT_NOTICIA, DT_INS_NOTICIA, TX_TITULO, TX_DESC_NOTICIA, TX_URL_FOTO_PEQ, TX_URL_FOTO_GRDE, TX_FONTE, TX_REDATOR, QT_VIST_PAG, DT_ULT_ACESSO) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_NOTICIA, :DT_NOTICIA, :DT_INS_NOTICIA, :TX_TITULO, :TX_DESC_NOTICIA, :TX_URL_FOTO_PEQ, :TX_URL_FOTO_GRDE, :TX_FONTE, :TX_REDATOR, :QT_VIST_PAG, :DT_ULT_ACESSO) ");

                    insertT_NOTICIA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_NOTICIA[index], ":ID_NOTICIA", DbType.Int64, o.ID_NOTICIA);
                    database.AddInParameter(insertT_NOTICIA[index], ":DT_NOTICIA", DbType.DateTime, o.DT_NOTICIA);
                    database.AddInParameter(insertT_NOTICIA[index], ":DT_INS_NOTICIA", DbType.DateTime, o.DT_INS_NOTICIA);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_TITULO", DbType.String, o.TX_TITULO);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_DESC_NOTICIA", DbType.String, o.TX_DESC_NOTICIA);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_URL_FOTO_PEQ", DbType.String, o.TX_URL_FOTO_PEQ);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_URL_FOTO_GRDE", DbType.String, o.TX_URL_FOTO_GRDE);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_FONTE", DbType.String, o.TX_FONTE);
                    database.AddInParameter(insertT_NOTICIA[index], ":TX_REDATOR", DbType.String, o.TX_REDATOR);
                    database.AddInParameter(insertT_NOTICIA[index], ":QT_VIST_PAG", DbType.Int32, o.QT_VIST_PAG);
                    database.AddInParameter(insertT_NOTICIA[index], ":DT_ULT_ACESSO", DbType.Object, o.DT_ULT_ACESSO);

                    insertT_NOTICIA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_NOTICIA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_NOTICIA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_NOTICIA SET ");
                SQL.Append("    DT_NOTICIA = str_to_date('" + obj.DT_NOTICIA.ToString("dd/MM/yyy") + "', '%d/%m/%Y') , ");
                
                if (!obj.DT_VIGENCIA.ToString("dd/MM/yyyy").Equals("01/01/0001"))
                    SQL.Append("    DT_VIGENCIA = str_to_date('" + obj.DT_VIGENCIA.ToString("dd/MM/yyy") + "', '%d/%m/%Y') , ");
                else
                    SQL.Append("    DT_VIGENCIA = null, ");
                SQL.Append("    TX_TITULO = '" + obj.TX_TITULO + "' , ");
                SQL.Append("    TX_DESC_NOTICIA = '" + obj.TX_DESC_NOTICIA + "' , ");
                SQL.Append("    TX_URL_FOTO_PEQ = '" + obj.TX_URL_FOTO_PEQ.Replace("\\", "#") + "' , ");
                SQL.Append("    TX_URL_FOTO_GRDE = '" + obj.TX_URL_FOTO_GRDE.Replace("\\", "#") + "' , ");
                SQL.Append("    TX_FONTE = '" + obj.TX_FONTE + "' , ");
                SQL.Append("    TX_REDATOR = '" + obj.TX_REDATOR + "'");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_NOTICIA = " + obj.ID_NOTICIA.ToString());

                DbCommand updateT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());

                updateT_NOTICIA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_NOTICIA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_NOTICIA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_NOTICIA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_NOTICIA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_NOTICIA SET ");
                    SQL.Append("    DT_NOTICIA = :DT_NOTICIA , ");
                    SQL.Append("    DT_INS_NOTICIA = :DT_INS_NOTICIA , ");
                    SQL.Append("    TX_TITULO = :TX_TITULO , ");
                    SQL.Append("    TX_DESC_NOTICIA = :TX_DESC_NOTICIA , ");
                    SQL.Append("    TX_URL_FOTO_PEQ = :TX_URL_FOTO_PEQ , ");
                    SQL.Append("    TX_URL_FOTO_GRDE = :TX_URL_FOTO_GRDE , ");
                    SQL.Append("    TX_FONTE = :TX_FONTE , ");
                    SQL.Append("    TX_REDATOR = :TX_REDATOR , ");
                    SQL.Append("    QT_VIST_PAG = :QT_VIST_PAG , ");
                    SQL.Append("    DT_ULT_ACESSO = :DT_ULT_ACESSO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_NOTICIA = :ID_NOTICIA  ");

                    updateT_NOTICIA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_NOTICIA[index], ":ID_NOTICIA", DbType.Int64, o.ID_NOTICIA);
                    database.AddInParameter(updateT_NOTICIA[index], ":DT_NOTICIA", DbType.DateTime, o.DT_NOTICIA);
                    database.AddInParameter(updateT_NOTICIA[index], ":DT_INS_NOTICIA", DbType.DateTime, o.DT_INS_NOTICIA);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_TITULO", DbType.String, o.TX_TITULO);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_DESC_NOTICIA", DbType.String, o.TX_DESC_NOTICIA);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_URL_FOTO_PEQ", DbType.String, o.TX_URL_FOTO_PEQ);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_URL_FOTO_GRDE", DbType.String, o.TX_URL_FOTO_GRDE);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_FONTE", DbType.String, o.TX_FONTE);
                    database.AddInParameter(updateT_NOTICIA[index], ":TX_REDATOR", DbType.String, o.TX_REDATOR);
                    database.AddInParameter(updateT_NOTICIA[index], ":QT_VIST_PAG", DbType.Int32, o.QT_VIST_PAG);
                    database.AddInParameter(updateT_NOTICIA[index], ":DT_ULT_ACESSO", DbType.Object, o.DT_ULT_ACESSO);

                    updateT_NOTICIA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_NOTICIA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_NOTICIA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_NOTICIA ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_NOTICIA = " + obj.ID_NOTICIA.ToString());

                DbCommand deleteT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());

                deleteT_NOTICIA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_NOTICIA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_NOTICIA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_NOTICIA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_NOTICIA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_NOTICIA ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_NOTICIA = :ID_NOTICIA  ");

                    deleteT_NOTICIA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_NOTICIA[index], ":ID_NOTICIA", DbType.Int64, o.ID_NOTICIA);

                    deleteT_NOTICIA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_NOTICIA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_NOTICIA obterPorPK(long pID_NOTICIA)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_NOTICIA ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_NOTICIA = " + pID_NOTICIA.ToString());

                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                T_NOTICIA obj = null;

                if (reader.Read())
                {
                    obj = new T_NOTICIA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_NOTICIA")))
                    {
                        obj.ID_NOTICIA = Int64.Parse(reader["ID_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_NOTICIA")))
                    {
                        obj.DT_NOTICIA = DateTime.Parse(reader["DT_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_INS_NOTICIA")))
                    {
                        obj.DT_INS_NOTICIA = DateTime.Parse(reader["DT_INS_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_VIGENCIA")))
                    {
                        obj.DT_VIGENCIA = DateTime.Parse(reader["DT_VIGENCIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DESC_NOTICIA")))
                    {
                        obj.TX_DESC_NOTICIA = reader["TX_DESC_NOTICIA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_PEQ")))
                    {
                        obj.TX_URL_FOTO_PEQ = reader["TX_URL_FOTO_PEQ"].ToString().Replace("#","\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_GRDE")))
                    {
                        obj.TX_URL_FOTO_GRDE = reader["TX_URL_FOTO_GRDE"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REDATOR")))
                    {
                        obj.TX_REDATOR = reader["TX_REDATOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_VIST_PAG")))
                    {
                        obj.QT_VIST_PAG = Int32.Parse(reader["QT_VIST_PAG"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ULT_ACESSO")))
                    {
                        obj.DT_ULT_ACESSO = (object)reader["DT_ULT_ACESSO"];
                    }
                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_NOTICIA> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_NOTICIA ORDER BY TX_DESC_NOTICIA");

                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                List<T_NOTICIA> lista = new List<T_NOTICIA>();
                T_NOTICIA obj = null;

                while (reader.Read())
                {
                    obj = new T_NOTICIA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_NOTICIA")))
                    {
                        obj.ID_NOTICIA = Int64.Parse(reader["ID_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_NOTICIA")))
                    {
                        obj.DT_NOTICIA = DateTime.Parse(reader["DT_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_VIGENCIA")))
                    {
                        obj.DT_VIGENCIA = DateTime.Parse(reader["DT_VIGENCIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_INS_NOTICIA")))
                    {
                        obj.DT_INS_NOTICIA = DateTime.Parse(reader["DT_INS_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DESC_NOTICIA")))
                    {
                        obj.TX_DESC_NOTICIA = reader["TX_DESC_NOTICIA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_PEQ")))
                    {
                        obj.TX_URL_FOTO_PEQ = reader["TX_URL_FOTO_PEQ"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_GRDE")))
                    {
                        obj.TX_URL_FOTO_GRDE = reader["TX_URL_FOTO_GRDE"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REDATOR")))
                    {
                        obj.TX_REDATOR = reader["TX_REDATOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_VIST_PAG")))
                    {
                        obj.QT_VIST_PAG = Int32.Parse(reader["QT_VIST_PAG"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ULT_ACESSO")))
                    {
                        obj.DT_ULT_ACESSO = (object)reader["DT_ULT_ACESSO"];
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<T_NOTICIA> consultar(string descricao,string redator)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_NOTICIA");
                SQL.Append(" WHERE TX_TITULO LIKE '" + descricao + "%'");

                if (!redator.Equals(""))
                    SQL.Append(" AND TX_REDATOR LIKE '" + redator + "%'");

                SQL.Append(" ORDER BY TX_TITULO");
                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                List<T_NOTICIA> lista = new List<T_NOTICIA>();
                T_NOTICIA obj = null;

                while (reader.Read())
                {
                    obj = new T_NOTICIA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_NOTICIA")))
                    {
                        obj.ID_NOTICIA = Int64.Parse(reader["ID_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_NOTICIA")))
                    {
                        obj.DT_NOTICIA = DateTime.Parse(reader["DT_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_VIGENCIA")))
                    {
                        obj.DT_VIGENCIA = DateTime.Parse(reader["DT_VIGENCIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_INS_NOTICIA")))
                    {
                        obj.DT_INS_NOTICIA = DateTime.Parse(reader["DT_INS_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DESC_NOTICIA")))
                    {
                        obj.TX_DESC_NOTICIA = reader["TX_DESC_NOTICIA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_PEQ")))
                    {
                        obj.TX_URL_FOTO_PEQ = reader["TX_URL_FOTO_PEQ"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_GRDE")))
                    {
                        obj.TX_URL_FOTO_GRDE = reader["TX_URL_FOTO_GRDE"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REDATOR")))
                    {
                        obj.TX_REDATOR = reader["TX_REDATOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_VIST_PAG")))
                    {
                        obj.QT_VIST_PAG = Int32.Parse(reader["QT_VIST_PAG"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ULT_ACESSO")))
                    {
                        obj.DT_ULT_ACESSO = (object)reader["DT_ULT_ACESSO"];
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<T_NOTICIA> consultarPorPeriodo(string mes,string ano)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" select * from t_noticia where YEAR(DT_NOTICIA) = '" + ano + "' and MONTH(DT_NOTICIA) = '" + mes + "'");                
                SQL.Append(" ORDER BY TX_TITULO");
                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                List<T_NOTICIA> lista = new List<T_NOTICIA>();
                T_NOTICIA obj = null;

                while (reader.Read())
                {
                    obj = new T_NOTICIA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_NOTICIA")))
                    {
                        obj.ID_NOTICIA = Int64.Parse(reader["ID_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_NOTICIA")))
                    {
                        obj.DT_NOTICIA = DateTime.Parse(reader["DT_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_INS_NOTICIA")))
                    {
                        obj.DT_INS_NOTICIA = DateTime.Parse(reader["DT_INS_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DESC_NOTICIA")))
                    {
                        obj.TX_DESC_NOTICIA = reader["TX_DESC_NOTICIA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_PEQ")))
                    {
                        obj.TX_URL_FOTO_PEQ = reader["TX_URL_FOTO_PEQ"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_GRDE")))
                    {
                        obj.TX_URL_FOTO_GRDE = reader["TX_URL_FOTO_GRDE"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REDATOR")))
                    {
                        obj.TX_REDATOR = reader["TX_REDATOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_VIST_PAG")))
                    {
                        obj.QT_VIST_PAG = Int32.Parse(reader["QT_VIST_PAG"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ULT_ACESSO")))
                    {
                        obj.DT_ULT_ACESSO = (object)reader["DT_ULT_ACESSO"];
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<T_NOTICIA> consultaDestaques(string limite)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" select * from t_noticia where DT_VIGENCIA >= str_to_date('" + DateTime.Now.ToString("dd/MM/yyy") + "', '%d/%m/%Y') ORDER BY DT_INS_NOTICIA DESC LIMIT " + limite);
                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                List<T_NOTICIA> lista = new List<T_NOTICIA>();
                T_NOTICIA obj = null;

                while (reader.Read())
                {
                    obj = new T_NOTICIA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_NOTICIA")))
                    {
                        obj.ID_NOTICIA = Int64.Parse(reader["ID_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_NOTICIA")))
                    {
                        obj.DT_NOTICIA = DateTime.Parse(reader["DT_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_INS_NOTICIA")))
                    {
                        obj.DT_INS_NOTICIA = DateTime.Parse(reader["DT_INS_NOTICIA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TITULO")))
                    {
                        obj.TX_TITULO = reader["TX_TITULO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DESC_NOTICIA")))
                    {
                        obj.TX_DESC_NOTICIA = reader["TX_DESC_NOTICIA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_PEQ")))
                    {
                        obj.TX_URL_FOTO_PEQ = reader["TX_URL_FOTO_PEQ"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO_GRDE")))
                    {
                        obj.TX_URL_FOTO_GRDE = reader["TX_URL_FOTO_GRDE"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_FONTE")))
                    {
                        obj.TX_FONTE = reader["TX_FONTE"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REDATOR")))
                    {
                        obj.TX_REDATOR = reader["TX_REDATOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_VIST_PAG")))
                    {
                        obj.QT_VIST_PAG = Int32.Parse(reader["QT_VIST_PAG"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ULT_ACESSO")))
                    {
                        obj.DT_ULT_ACESSO = (object)reader["DT_ULT_ACESSO"];
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<String> listarAno()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append("select distinct(YEAR(DT_NOTICIA)) as ano from t_noticia order by YEAR(DT_NOTICIA) desc");                

                DbCommand consultaT_NOTICIA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_NOTICIA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_NOTICIA, DALConnection.transaction);
                List<String> lista = new List<String>();                

                while (reader.Read())
                {
                    if (!reader.IsDBNull(reader.GetOrdinal("ano")))
                    {
                        lista.Add(reader["ano"].ToString());
                    }
                }
                reader.Dispose();
                consultaT_NOTICIA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}

